package com.example.demo;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import com.example.demo.Controller.SQLController;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@RestController
public class GetListIndexController {
    @PostMapping("/getListIndex")
    public JSONObject getListIndex(@RequestBody JSONObject requestBody) {
        JSONObject json = new JSONObject();
        JSONArray arrayPlayer = new JSONArray();
        String keyword = requestBody.getString("keyword");
        String order = requestBody.getString("order");
        String wxid= (String) requestBody.get("wxid");
        SQLController sqlController=new SQLController();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            connection=sqlController.ConnectToSql("jdbc:mysql://localhost:3306/jidazhidemai","root","gFL123456"+"");
            String sql = "SELECT post.postid, post.name, post.location, post.like, post.dislike, post.comments_longth,post.oldprice,post.newprice,post.time\n" +
                    "FROM post LEFT JOIN user ON user.userid = post.posterid \n" +
                    "WHERE 1=1 ";

            if (keyword != null && !keyword.equals("")) {
                //sql += "AND post.name LIKE ?";
                sql+="AND (post.name LIKE ? OR post.location LIKE ?)";
            }

            sql += " GROUP BY post.postid ";

            int index=1;
            if (order != null && !order.equals("")) {
                sql += " ORDER BY CASE \n" +
                        "    WHEN ?='time' THEN post.time \n" +
                        "    WHEN ?='hot' THEN post.comments_longth+post.like+post.dislike \n" +
                        "    WHEN ?='upvote' THEN post.like-post.dislike \n" +
                        "    ELSE post.postid END DESC";
                preparedStatement = connection.prepareStatement(sql);
                if (keyword != null && !keyword.equals("")) {
                    preparedStatement.setString(index, "%" + keyword + "%");
                    preparedStatement.setString(index + 1, "%" + keyword + "%");
                    preparedStatement.setString(index + 2, order);
                    preparedStatement.setString(index + 3, order);
                    preparedStatement.setString(index + 4, order);
                } else {
                    preparedStatement.setString(index, order);
                    preparedStatement.setString(index + 1, order);
                    preparedStatement.setString(index + 2, order);
                }
            } else {
                sql += " ORDER BY post.time DESC";
                preparedStatement = connection.prepareStatement(sql);
                if (keyword != null && !keyword.equals("")) {
                    preparedStatement.setString(index, "%" + keyword + "%");
                    preparedStatement.setString(index + 1, "%" + keyword + "%");
                }
            }

            if (keyword != null && !keyword.equals("")) {
                preparedStatement.setString(index, "%" + keyword + "%");
            }

            resultSet = preparedStatement.executeQuery();


            while (resultSet.next()) {
                JSONObject data = new JSONObject();
                data.put("postid", resultSet.getInt("postid"));
                data.put("name", resultSet.getString("name"));
                data.put("location", resultSet.getString("location"));
                data.put("like", resultSet.getInt("like"));
                data.put("dislike", resultSet.getInt("dislike"));
                data.put("comments", resultSet.getInt("comments_longth"));
                Boolean fav = queryfav(wxid, String.valueOf(resultSet.getInt("postid")));
                data.put("fav", fav);
                data.put("oldprice", resultSet.getDouble("oldprice"));
                data.put("newprice", resultSet.getDouble("newprice"));
                data.put("time", resultSet.getString("time"));
                arrayPlayer.add(data);
            }

            if (arrayPlayer.size() == 0) {
                json.put("status", "COMPLETE");
                json.put("list", arrayPlayer);
            } else {
                json.put("status", "COMPLETE");
                json.put("list", arrayPlayer);
            }
            connection.close();
        } catch (SQLException e) {
            json.put("status", "FAIL");
            json.put("message", "An error occurred while accessing the database.");
            e.printStackTrace();
        } catch (JSONException e) {
            json.put("status", "FAIL");
            json.put("message", "Invalid input data format.");
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        System.out.println("GetListIndex");
        System.out.println(json);
        return json;
    }
    public static boolean queryfav (String wxid, String postid){
        Connection con;
        try {
            String driver = "com.mysql.cj.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/jidazhidemai";

            // 数据库的用户名与密码，需要根据自己的设置
            String user = "root";
            String password = "gFL123456";
            Class.forName(driver);
            //注册JDBC驱动程 序
            //建立连接
            con = DriverManager.getConnection(url, user, password);
            if (!con.isClosed()) {
                System.out.println("数据库连接成功");
            }

            //Statement stmt = con.createStatement();
//            String sql = "INSERT INTO tab1 (title,author,submission_date) VALUES ('li','xiao','2020-07028')";
            String sql = " select fav from user where wxid=?;";
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1, wxid);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                String fav = rs.getString("fav");
                if(fav==null){
                    con.close();
                    return false;
                }
                List<String> result = new ArrayList<String>(Arrays.asList(fav.split(",")));
                System.out.println("queryfav");
                System.out.println(result);
                System.out.println(postid);
                if (result.contains(postid)) {
                    System.out.println(true);
                    con.close();
                    return true;
                } else {
                    con.close();
                    return false;
                }
            }


        } catch (ClassNotFoundException e) {
            System.out.println("数据库驱动没有安装");

        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败");
        }

        return false;
    }

}
